﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;

/// <summary>
/// Summary description for SqlConnectionRun
/// </summary>
public abstract class SqlConnectionRun
{
    public SqlConnection sqlConnection = null;
	public SqlConnectionRun()
	{
        //string strConnect = ConfigurationManager.ConnectionStrings["strConnect"].ConnectionString;
        sqlConnection = new SqlConnection("server=server2;database=PhongKham;uid=sa;pwd=");
	}

    public DataTable SqlExecuteQueryReader(string query, List<SqlParameter> parameters = null)
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandText = query;
        if (parameters != null)
            sqlCommand.Parameters.AddRange(parameters.ToArray());
        SqlDataReader reader = sqlCommand.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        sqlConnection.Close();
        return dt;
    }


    public DataTable SqlRunStoredProcedure(string store, List<SqlParameter> parameters = null)
    {                  
        sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandText = store;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
            sqlCommand.Parameters.AddRange(parameters.ToArray());
        SqlDataReader reader = sqlCommand.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(reader);
        sqlConnection.Close();
        return dt;
    }

    public int SqlRunStoredProcedureNoneQuery(string store, List<SqlParameter> parameters = null)
    {
        if(sqlConnection.State == ConnectionState.Closed) sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandText = store;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
            sqlCommand.Parameters.AddRange(parameters.ToArray());
        int result = sqlCommand.ExecuteNonQuery();        
        sqlConnection.Close();
        return result;
    }

    public object SqlRunStoredProcedureScalar(string store, List<SqlParameter> parameters = null)
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandText = store;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
            sqlCommand.Parameters.AddRange(parameters.ToArray());
        object result = sqlCommand.ExecuteScalar();
        sqlConnection.Close();
        return result;
    }

    public DataSet SqlExecuteQueryFillDataSet(string query)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(query, sqlConnection);
        adapter.Fill(ds);
        sqlConnection.Close();
        return ds;
    }

    public object SqlExecuteQueryScalar(string query, List<SqlParameter> parameters = null)
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandText = query;
        if (parameters != null)
            sqlCommand.Parameters.AddRange(parameters.ToArray());
        object result = sqlCommand.ExecuteScalar();
        sqlConnection.Close();
        return result;
    }

    public int SqlExecuteNoneQuery(string query, List<SqlParameter> parameters = null)
    {
        sqlConnection.Open();
        SqlCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandText = query;
        if(parameters != null)
            sqlCommand.Parameters.AddRange(parameters.ToArray());
        int result = sqlCommand.ExecuteNonQuery();        
        sqlConnection.Close();
        return result;
    }
}